/*
 * 文 件 名:  ExportExcel.java
 * 版    权:  changjet Co., Ltd. Copyright 2010-2011,  All rights reserved
 * 描    述:  <描述>
 * 修 改 人:  张卓卫
 * 修改时间:  2012-1-12
 * 跟踪单号:  <跟踪单号>
 * 修改单号:  <修改单号>
 * 修改内容:  <修改内容>
 */
package com.kj.tms.common.utils.excel;

import com.kj.tms.common.bean.dto.BaseDto;
import com.kj.tms.common.utils.StringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * <一句话功能简述> <功能详细描述>
 * 
 * @author zhangzhw
 * @version [版本号, 2012-1-12]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class ExportExcel {
	protected static Logger logger = LoggerFactory.getLogger(ExportExcel.class);

	/**
	 * 
	 * @param title
	 *            表格标题名
	 * @param header
	 *            表格属性列名数组
	 * @param dataset
	 *            数据集
	 * 
	 * @param colNames
	 *            数据集列名
	 * 
	 * @param out
	 */
	@SuppressWarnings({"deprecation", "rawtypes", "unchecked"})
	public static void exportExcel(OutputStream out, String title, String describe, String[] header, String[] colNames,
			Collection dataset) {

		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(title);
			HSSFFont fontinfo = wb.createFont();
			fontinfo.setFontHeightInPoints((short) 8); // 字体大小

			HSSFFont fonthead = wb.createFont();
			fonthead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			fonthead.setFontHeightInPoints((short) 10); // 字体大小
			HSSFCellStyle cellStylename = wb.createCellStyle();// 表名样式
			cellStylename.setFont(fonthead);
			HSSFCellStyle cellStyleinfo = wb.createCellStyle();// 表信息样式
			cellStyleinfo.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 对齐
			cellStyleinfo.setFont(fontinfo);

			HSSFCellStyle cellStyleTitle = wb.createCellStyle();// 表头样式
			cellStyleTitle.setFont(fonthead);
			cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 边框
			cellStyleTitle.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setTopBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle cellStylehead = wb.createCellStyle();// 表头样式
			cellStylehead.setFont(fonthead);
			cellStylehead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStylehead.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 边框
			cellStylehead.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setRightBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setTopBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStylehead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle cellStyle = wb.createCellStyle();// 数据单元样式
			cellStyle.setWrapText(true);// 自动换行
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			HSSFDataFormat format = wb.createDataFormat();
			cellStyle.setDataFormat(format.getFormat("@"));
			HSSFFont font3 = wb.createFont();
			font3.setColor(HSSFColor.BLUE.index);

			int col = header.length;
			

			HSSFRow row = sheet.createRow((short) 0);
			HSSFCell cell = row.createCell((short) 0);
			cell.setCellStyle(cellStyleTitle);
			cell.setCellValue(title); // 信息
			sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) col));

			HSSFRow describeRow = sheet.createRow((short) 1);
			HSSFCell describeCell = describeRow.createCell((short) 0);
			describeCell.setCellStyle(cellStyleTitle);
			describeCell.setCellValue(describe); // 信息
			sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) col));

			for (int i = 1; i <= header.length; i++) {
				sheet.setColumnWidth((short) i, (short) 6000);
			}

			int rows_max = 0;
			for (int i = 0; i < header.length; i++) {
				String h = header[i];
				if (h.split("_").length > rows_max) {
					rows_max = h.split("_").length;
				}
			}
			Map map = new HashMap();
			for (int k = 0; k < rows_max; k++) {

				row = sheet.createRow((short) k + 2);
				if (k == 0) {
					cell = row.createCell((short) (0));
					cell.setCellStyle(cellStylehead);
					cell.setCellValue("序号");
					sheet.addMergedRegion(new Region(k + 2, (short) 0, k + 1 + rows_max, (short) 0));
				}
				for (int i = 0; i < header.length; i++) {
					String headerTemp = header[i];
					String[] s = headerTemp.split("_");
					String sk = "";
					int num = i + 1;
					if (s.length == 1) {
						cell = row.createCell((short) (num));
						cell.setCellStyle(cellStylehead);
						sheet.addMergedRegion(new Region(2, (short) (num), rows_max + 1, (short) (num)));
						sk = headerTemp;
						cell.setCellValue(sk);
					} else {
						// System.out.println(sk);

						cell = row.createCell((short) (num));
						cell.setCellStyle(cellStylehead);
						int cols = 0;
						if (map.containsKey(headerTemp)) {
							continue;
						}
						for (int d = 0; d <= k; d++) {
							if (d != k) {
								sk += s[d] + "_";
							} else {
								sk += s[d];
							}
						}
						if (map.containsKey(sk)) {
							continue;
						}
						for (int j = 0; j < header.length; j++) {
							if (header[j].indexOf(sk) != -1) {
								cols++;
							}
						}
						cell.setCellValue(s[k]);
						sheet.addMergedRegion(new Region(k + 2, (short) num, k + 2, (short) (num + cols - 1)));
						if (sk.equals(headerTemp)) {
							sheet.addMergedRegion(new Region(k + 2, (short) num, k + 2 + rows_max - s.length,
									(short) num));
						}
					}
					if (s.length > k) {
						if (!map.containsKey(sk)) {
							String key = "";
							if (k > 0) {
								key = sk;
							} else {
								key = s[k];
							}
							map.put(key, null);
						}
					}
				}
			}
			// 遍历集合数据，产生数据行
			if (dataset != null) {
				Iterator it = dataset.iterator();
				int index = rows_max + 1;
				while (it.hasNext()) {
					index++;
					row = sheet.createRow(index);
					HSSFCell CelRowNum = row.createCell(0);
					CelRowNum.setCellStyle(cellStyle);
					int l = (index - rows_max - 1);
					// HSSFRichTextString s = new
					// HSSFRichTextString(String.valueOf(l));
					// s.applyFont(font3);
					CelRowNum.setCellValue(l);

					Map t = (Map<String, String>) it.next();
					for (short i = 1; i <= colNames.length; i++) {
						HSSFCell dataCell = row.createCell(i);
						dataCell.setCellStyle(cellStyle);
						String colName = colNames[i - 1];
						 try {
							// 判断值的类型后进行强制类型转换
							String textValue = null;
							Object obj = t.get(colName);
							if (obj != null) {
								textValue = obj.toString();
							}
							// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
							if (textValue != null) {
								Pattern doublePattern = Pattern.compile("^\\d+(\\.\\d+)?$");
								// Pattern intPattern =
								// Pattern.compile("^-?\\d+$");
								// Matcher matcher1 =
								// doublePattern.matcher(textValue);
								// Matcher matcher =
								// intPattern.matcher(textValue);
								// if (matcher1.matches()) {
								// // 是数字当作double处理
								// dataCell.setCellValue(Double.parseDouble(textValue));
								// } else {
								// add by huanghm用于处理*号问题
								if (textValue.equals("-999999999999999")) {
									textValue = "*";
								}
								if (textValue.equals("-99999999999998")) {
									textValue = "0";
								}
								HSSFRichTextString richString = new HSSFRichTextString(textValue);

								richString.applyFont(font3);
								dataCell.setCellValue(richString);
								// }
							}
						} catch (SecurityException e) {
							throw new Exception("导出数据出错！", e);
						} catch (IllegalArgumentException e) {
						} finally {
							// 清理资源
						}
					}

				}
			}
			wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<Map> Object2Map(List<BaseDto> list, String fieldName1) throws Exception {
		List<Map> dataset = new ArrayList<Map>();
		DecimalFormat df = new DecimalFormat("####.####");
		for (int i = 0; i < list.size(); i++) {
			Map<String, String> map = new HashMap<String, String>();
			// 取得对象的fields
			Field[] childFields = list.get(i).getClass().getDeclaredFields();
			// 取得父类对象的fields
			Field[] supperFields = list.get(i).getClass().getSuperclass().getDeclaredFields();
			// 合并对象的fields
			int fieldLength = childFields.length + supperFields.length;
			Field[] fields = new Field[fieldLength];
			int k = 0;
			for (k = 0; k < childFields.length; k++) {
				fields[k] = childFields[k];
			}
			for (int kk = 0; kk < supperFields.length; kk++, k++) {
				fields[k] = supperFields[kk];
			}

			BaseDto baseDto = list.get(i);
			for (int j = 0; j < fields.length; j++) {
				if (!fields[j].isAccessible()) {
					fields[j].setAccessible(true);
				}
				// 属性名称：属性值
				Object value = null;
				String fieldName = "";
				try {
					value = fields[j].get(baseDto);

					fieldName = fields[j].getName();

				} catch (IllegalArgumentException e1) {
					throw new IllegalArgumentException("导出数据出错！", e1);
				} catch (IllegalAccessException e2) {
					throw new IllegalAccessException("导出数据出错！");
				}

				if (value instanceof BaseDto) {
					List<BaseDto> dtols = new ArrayList<BaseDto>();
					dtols.add((BaseDto) value);
					map.putAll(Object2Map(dtols, fieldName).get(0));
				} else if (value instanceof Double) {
					String svalue = df.format(value);
					if ("".equals(fieldName1)) {
						map.put(fieldName, svalue);
					} else {
						map.put(fieldName1 + "." + fieldName, svalue);
					}

				} else {
					if ("".equals(fieldName1)) {
						map.put(fieldName, value == null ? "" : String.valueOf(value));
					} else {
						map.put(fieldName1 + "." + fieldName, value == null ? "" : String.valueOf(value));
					}
				}

			}
			dataset.add(map);
		}
		return dataset;
	}

	@SuppressWarnings("deprecation")
	public static void exportPageExcel(OutputStream out, String title, String describe, String[] header,
			String[] colNames, Collection dataset, int pageSize) {
		int count = 0;
		try {
			HSSFWorkbook wb = new HSSFWorkbook();

			HSSFFont fontinfo = wb.createFont();
			fontinfo.setFontHeightInPoints((short) 8); // 字体大小

			HSSFFont fonthead = wb.createFont();
			fonthead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			fonthead.setFontHeightInPoints((short) 10); // 字体大小
			HSSFCellStyle cellStylename = wb.createCellStyle();// 表名样式
			cellStylename.setFont(fonthead);
			HSSFCellStyle cellStyleinfo = wb.createCellStyle();// 表信息样式
			cellStyleinfo.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 对齐
			cellStyleinfo.setFont(fontinfo);

			HSSFCellStyle cellStyleTitle = wb.createCellStyle();// 表头样式
			cellStyleTitle.setFont(fonthead);
			cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 边框
			cellStyleTitle.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyleTitle.setTopBorderColor(HSSFColor.BLACK.index);
			cellStyleTitle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle cellStylehead = wb.createCellStyle();// 表头样式
			cellStylehead.setFont(fonthead);
			cellStylehead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStylehead.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 边框
			cellStylehead.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setRightBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStylehead.setTopBorderColor(HSSFColor.BLACK.index);
			cellStylehead.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStylehead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			HSSFCellStyle cellStyle = wb.createCellStyle();// 数据单元样式
			cellStyle.setWrapText(true);// 自动换行
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			HSSFDataFormat format = wb.createDataFormat();
			cellStyle.setDataFormat(format.getFormat("@"));
			HSSFFont font3 = wb.createFont();
			font3.setColor(HSSFColor.BLUE.index);

			int col = header.length;
			if (dataset != null && dataset.size() > 0) {
				Iterator it = dataset.iterator();

				int sheetCount = dataset.size() % pageSize == 0 ? dataset.size() / pageSize : dataset.size() / pageSize
						+ 1;
				for (int i1 = 1; i1 <= sheetCount; i1++) {
					HSSFSheet sheet = null;
					if (!StringUtil.isEmpty(title)) {
						sheet = wb.createSheet(title + i1);
					} else {
						sheet = wb.createSheet();
					}
					// HSSFSheet sheet = wb.createSheet(title);

					HSSFRow row = sheet.createRow((short) 0);
					HSSFCell cell = row.createCell((short) 0);
					cell.setCellStyle(cellStyleTitle);
					cell.setCellValue(title); // 信息
					sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) col));

					HSSFRow describeRow = sheet.createRow((short) 1);
					HSSFCell describeCell = describeRow.createCell((short) 0);
					describeCell.setCellStyle(cellStyleTitle);
					describeCell.setCellValue(describe); // 信息
					sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) col));

					for (int i = 1; i <= header.length; i++) {
						sheet.setColumnWidth((short) i, (short) 6000);
					}

					int rows_max = 0;
					for (int i = 0; i < header.length; i++) {
						String h = header[i];
						if (h.split("_").length > rows_max) {
							rows_max = h.split("_").length;
						}
					}
					Map map = new HashMap();
					for (int k = 0; k < rows_max; k++) {

						row = sheet.createRow((short) k + 2);
						if (k == 0) {
							cell = row.createCell((short) (0));
							cell.setCellStyle(cellStylehead);
							cell.setCellValue("序号");
							sheet.addMergedRegion(new Region(k + 2, (short) 0, k + 1 + rows_max, (short) 0));
						}
						for (int i = 0; i < header.length; i++) {
							String headerTemp = header[i];
							String[] s = headerTemp.split("_");
							String sk = "";
							int num = i + 1;
							if (s.length == 1) {
								cell = row.createCell((short) (num));
								cell.setCellStyle(cellStylehead);
								sheet.addMergedRegion(new Region(2, (short) (num), rows_max + 1, (short) (num)));
								sk = headerTemp;
								cell.setCellValue(sk);
							} else {
								// System.out.println(sk);

								cell = row.createCell((short) (num));
								cell.setCellStyle(cellStylehead);
								int cols = 0;
								if (map.containsKey(headerTemp)) {
									continue;
								}
								for (int d = 0; d <= k; d++) {
									if (d != k) {
										sk += s[d] + "_";
									} else {
										sk += s[d];
									}
								}
								if (map.containsKey(sk)) {
									continue;
								}
								for (int j = 0; j < header.length; j++) {
									if (header[j].indexOf(sk) != -1) {
										cols++;
									}
								}
								cell.setCellValue(s[k]);
								sheet.addMergedRegion(new Region(k + 2, (short) num, k + 2, (short) (num + cols - 1)));
								if (sk.equals(headerTemp)) {
									sheet.addMergedRegion(new Region(k + 2, (short) num, k + 2 + rows_max - s.length,
											(short) num));
								}
							}
							if (s.length > k) {
								if (!map.containsKey(sk)) {
									String key = "";
									if (k > 0) {
										key = sk;
									} else {
										key = s[k];
									}
									map.put(key, null);
								}
							}
						}
					}

					// 遍历集合数据，产生数据行

					int index = rows_max + 1;
					while (it.hasNext()) {
						count++;
						index++;
						row = sheet.createRow(index);
						HSSFCell CelRowNum = row.createCell(0);
						CelRowNum.setCellStyle(cellStyle);
						int l = (index - rows_max - 1);
						CelRowNum.setCellValue(count);

						Map t = (Map<String, String>) it.next();
						for (short i = 1; i <= colNames.length; i++) {
							HSSFCell dataCell = row.createCell(i);
							dataCell.setCellStyle(cellStyle);
							String colName = colNames[i - 1];
							try {
								// 判断值的类型后进行强制类型转换
								String textValue = null;
								Object obj = t.get(colName);
								if (obj != null) {
									textValue = obj.toString();
								}
								// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
								if (textValue != null) {
									Pattern doublePattern = Pattern.compile("^\\d+(\\.\\d+)?$");
									if (textValue.equals("-999999999999999")) {
										textValue = "*";
									}
									if (textValue.equals("-99999999999998")) {
										textValue = "0";
									}
									HSSFRichTextString richString = new HSSFRichTextString(textValue);

									richString.applyFont(font3);
									dataCell.setCellValue(richString);
									// }
								}
							} catch (SecurityException e) {
								throw new SecurityException("导出数据出错！", e);
							} catch (IllegalArgumentException e) {
							} finally {
								// 清理资源
							}
						}
						if(count==pageSize*i1){
							break;
						}
					}
				}
			}
			wb.write(out);
		} catch (Exception e) {
			throw new SecurityException("导出数据出错！", e);
		}
	}

}